from flask import Flask, render_template, request, jsonify, send_file, redirect, url_for, session
from flask_sqlalchemy import SQLAlchemy
import os
import json
import pandas as pd
from datetime import datetime
import io
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from werkzeug.security import generate_password_hash, check_password_hash
from flask import Flask, request, jsonify, render_template, redirect, url_for, session

app = Flask(__name__)
# 配置SQLite数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students_score.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.secret_key = 'your-secret-key-123456'  # 设置一个密钥，用于session加密

# 配置文件上传
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'xlsx', 'xls'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# 确保上传文件夹存在
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

# 检查文件扩展名是否允许
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

db = SQLAlchemy(app)

# 用户模型
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(120), nullable=False)
    role = db.Column(db.String(20), default='user')  # 'admin' 或 'user'
    created_at = db.Column(db.DateTime, default=datetime.now)
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
        
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

# 登录装饰器，用于保护需要登录的路由
def login_required(f):
    from functools import wraps
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# 管理员装饰器，用于保护需要管理员权限的路由
def admin_required(f):
    from functools import wraps
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        user = User.query.get(session['user_id'])
        if user.role != 'admin':
            return jsonify({'success': False, 'message': '需要管理员权限'}), 403
        return f(*args, **kwargs)
    return decorated_function

# 定义数据库模型

# 学生表
class Student(db.Model):
    __tablename__ = 'students'
    student_id = db.Column(db.String(20), primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    grade = db.Column(db.String(10), nullable=False)
    major = db.Column(db.String(50), nullable=False)
    class_name = db.Column(db.String(20), nullable=False)
    scores = db.relationship('Score', backref='student', lazy=True)

# 项目表
class Project(db.Model):
    __tablename__ = 'projects'
    id = db.Column(db.String(20), primary_key=True)
    module = db.Column(db.String(50), nullable=False)
    category = db.Column(db.String(20), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    unit = db.Column(db.String(50), nullable=False)
    score = db.Column(db.Float, nullable=False)
    remark = db.Column(db.String(200))
    scores = db.relationship('Score', backref='project', lazy=True)

# 成绩表 - 这是学生和项目之间的关联表
class Score(db.Model):
    __tablename__ = 'scores'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    student_id = db.Column(db.String(20), db.ForeignKey('students.student_id'), nullable=False)
    project_id = db.Column(db.String(20), db.ForeignKey('projects.id'), nullable=False)

# 计算结果表
class CalculationResult(db.Model):
    __tablename__ = 'calculation_results'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    student_id = db.Column(db.String(20), db.ForeignKey('students.student_id'), nullable=False)
    name = db.Column(db.String(50), nullable=False)
    module_name = db.Column(db.String(50), nullable=False)
    score = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now)
    # 建立与学生表的关系
    student = db.relationship('Student', backref='calculation_results', lazy=True)

# 创建数据库和表
@app.cli.command("init-db")
def init_db():
    with app.app_context():
        db.create_all()
        
        # 添加默认管理员用户
        admin_user = User(username='admin', role='admin')
        admin_user.set_password('admin123')  # 默认密码
        db.session.add(admin_user)
        
        # 添加一些初始数据以便测试
        # 学生数据
        students = [
            Student(student_id='2021001', name='张三', grade='2021', major='计算机科学', class_name='计科1班'),
            Student(student_id='2021002', name='李四', grade='2021', major='计算机科学', class_name='计科1班'),
            Student(student_id='2021003', name='王五', grade='2021', major='软件工程', class_name='软工1班'),
            Student(student_id='2022001', name='赵六', grade='2022', major='计算机科学', class_name='计科2班'),
            Student(student_id='2022002', name='钱七', grade='2022', major='软件工程', class_name='软工2班'),
        ]
        # 项目数据
        projects = [
            Project(id='P001', module='学术竞赛', category='国家级', name='ACM程序设计竞赛', unit='教育部', score=10, remark='全国总决赛获奖'),
            Project(id='P002', module='学术竞赛', category='省级', name='数学建模竞赛', unit='省教育厅', score=6, remark='省级一等奖'),
            Project(id='P003', module='科研项目', category='校级', name='大学生创新创业训练计划', unit='学校', score=4, remark='校级立项'),
            Project(id='P004', module='社会实践', category='校级', name='暑期三下乡', unit='学校团委', score=3, remark='优秀团队'),
            Project(id='P005', module='文体活动', category='校级', name='校运动会', unit='学校体育部门', score=2, remark='个人项目获奖'),
        ]
        # 成绩数据
        scores = [
            Score(student_id='2021001', project_id='P001'),
            Score(student_id='2021001', project_id='P002'),
            Score(student_id='2021002', project_id='P003'),
            Score(student_id='2021003', project_id='P004'),
            Score(student_id='2022001', project_id='P005'),
            Score(student_id='2022002', project_id='P002'),
        ]
        
        db.session.add_all(students)
        db.session.add_all(projects)
        db.session.add_all(scores)
        db.session.commit()
        print('数据库初始化成功，已添加初始数据和默认管理员用户！')
        print('默认管理员账号: admin')
        print('默认管理员密码: admin123')

# 首页路由
@app.route('/')
@login_required
def index():
    return render_template('index.html', username=session.get('username'), role=session.get('role'))

# 登录路由
@app.route('/login', methods=['GET', 'POST'])
def login():
    if 'user_id' in session:
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        user = User.query.filter_by(username=username).first()
        if user and user.check_password(password):
            session['user_id'] = user.id
            session['username'] = user.username
            session['role'] = user.role
            return redirect(url_for('index'))
        else:
            return render_template('login.html', error='用户名或密码错误')
    
    return render_template('login.html')

# 注销路由
@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('username', None)
    session.pop('role', None)
    return redirect(url_for('login'))

# 注册路由（仅允许管理员注册）
@app.route('/register', methods=['GET', 'POST'])
@admin_required
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        role = request.form['role']
        
        # 检查用户名是否已存在
        if User.query.filter_by(username=username).first():
            return render_template('register.html', error='用户名已存在')
        
        # 创建新用户
        new_user = User(username=username, role=role)
        new_user.set_password(password)
        
        try:
            db.session.add(new_user)
            db.session.commit()
            return redirect(url_for('login'))
        except:
            db.session.rollback()
            return render_template('register.html', error='注册失败，请重试')
    
    return render_template('register.html')

# 用户管理路由（仅允许管理员访问）
@app.route('/user_management')
@admin_required
def user_management():
    users = User.query.order_by(User.id).all()
    current_user_id = session.get('user_id')
    return render_template('user_management.html', users=users, 
                          username=session.get('username'), 
                          role=session.get('role'),
                          current_user_id=current_user_id)

# 编辑用户路由（仅允许管理员访问）
@app.route('/edit_user/<int:user_id>', methods=['GET', 'POST'])
@admin_required
def edit_user(user_id):
    user = User.query.get_or_404(user_id)
    
    # 不允许修改自己的角色为普通用户，如果当前只有一个管理员
    if user_id == session.get('user_id'):
        admin_count = User.query.filter_by(role='admin').count()
        if admin_count <= 1:
            # 禁止将最后一个管理员降级为普通用户
            only_admin = True
        else:
            only_admin = False
    else:
        only_admin = False
    
    if request.method == 'POST':
        # 检查新的用户名是否已存在（排除当前用户）
        new_username = request.form['username']
        existing_user = User.query.filter(User.username == new_username, User.id != user_id).first()
        if existing_user:
            return render_template('edit_user.html', user=user, error='用户名已存在')
        
        # 更新用户名
        user.username = new_username
        
        # 更新角色（如果允许）
        new_role = request.form['role']
        if not only_admin or new_role == 'admin':
            user.role = new_role
        
        # 更新密码（如果提供了新密码）
        new_password = request.form['password']
        if new_password:
            user.set_password(new_password)
        
        try:
            db.session.commit()
            return redirect(url_for('user_management', success='用户信息更新成功'))
        except:
            db.session.rollback()
            return render_template('edit_user.html', user=user, error='更新失败，请重试')
    
    return render_template('edit_user.html', user=user)

# 删除用户路由（仅允许管理员访问）
@app.route('/delete_user/<int:user_id>')
@admin_required
def delete_user(user_id):
    # 不允许删除当前登录的用户
    if user_id == session.get('user_id'):
        return redirect(url_for('user_management', error='不能删除当前登录的用户'))
    
    user = User.query.get_or_404(user_id)
    
    # 不允许删除最后一个管理员
    if user.role == 'admin':
        admin_count = User.query.filter_by(role='admin').count()
        if admin_count <= 1:
            return redirect(url_for('user_management', error='不能删除最后一个管理员'))
    
    try:
        db.session.delete(user)
        db.session.commit()
        return redirect(url_for('user_management', success='用户已成功删除'))
    except:
        db.session.rollback()
        return redirect(url_for('user_management', error='删除失败，请重试'))

# API路由

# 获取学生列表
@app.route('/api/students', methods=['GET'])
@login_required
def get_students():
    search = request.args.get('search', '').lower()
    if search:
        students = Student.query.filter(
            (Student.student_id.ilike(f'%{search}%')) | 
            (Student.name.ilike(f'%{search}%')) |
            (Student.grade.ilike(f'%{search}%')) |
            (Student.major.ilike(f'%{search}%')) |
            (Student.class_name.ilike(f'%{search}%'))
        ).all()
    else:
        students = Student.query.all()
    
    result = []
    for student in students:
        result.append({
            'student_id': student.student_id,
            'name': student.name,
            'grade': student.grade,
            'major': student.major,
            'class_name': student.class_name
        })
    
    return jsonify(result)

# 获取项目列表
@app.route('/api/projects', methods=['GET'])
@login_required
def get_projects():
    search = request.args.get('search', '').lower()
    if search:
        projects = Project.query.filter(
            (Project.id.ilike(f'%{search}%')) | 
            (Project.name.ilike(f'%{search}%')) |
            (Project.module.ilike(f'%{search}%')) |
            (Project.category.ilike(f'%{search}%')) |
            (Project.unit.ilike(f'%{search}%'))
        ).all()
    else:
        projects = Project.query.all()
    
    result = []
    for project in projects:
        result.append({
            'id': project.id,
            'module': project.module,
            'category': project.category,
            'name': project.name,
            'unit': project.unit,
            'score': project.score,
            'remark': project.remark
        })
    
    return jsonify(result)

# 获取成绩列表
@app.route('/api/scores', methods=['GET'])
@login_required
def get_scores():
    search = request.args.get('search', '').lower()
    
    if search:
        # 复杂查询，需要关联三个表
        scores = Score.query.join(Student).join(Project).filter(
            (Student.student_id.ilike(f'%{search}%')) | 
            (Student.name.ilike(f'%{search}%')) |
            (Project.name.ilike(f'%{search}%')) |
            (Project.id.ilike(f'%{search}%'))
        ).all()
    else:
        scores = Score.query.all()
    
    result = []
    for score in scores:
        # 安全地获取学生姓名和项目名称，处理可能的None值
        student_name = score.student.name if score.student else '未知学生'
        project_name = score.project.name if score.project else '未知项目'
        
        result.append({
            'student_id': score.student_id,
            'student_name': student_name,
            'project_id': score.project_id,
            'project_name': project_name
        })
    
    return jsonify(result)

# 导入学生数据API
@app.route('/api/import/students', methods=['POST'])
@admin_required
def import_students():
    try:
        # 检查是否有文件上传
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '没有文件上传'})
        
        file = request.files['file']
        
        # 检查文件名是否为空
        if file.filename == '':
            return jsonify({'success': False, 'message': '没有选择文件'})
        
        # 检查文件类型是否允许
        if not allowed_file(file.filename):
            return jsonify({'success': False, 'message': '只允许上传Excel文件(xlsx, xls)'})
        
        # 读取Excel文件
        df = pd.read_excel(io.BytesIO(file.read()))
        
        # 检查必要的列是否存在
        required_columns = ['学号', '姓名', '年级', '专业', '班级']
        for col in required_columns:
            if col not in df.columns:
                return jsonify({'success': False, 'message': f'Excel文件中缺少必要的列：{col}'})
        
        # 导入数据前先清空数据表
        imported_count = 0
        
        with app.app_context():
            # 清空学生表中的所有数据
            db.session.query(Student).delete()
            db.session.commit()
            
            # 重新开始事务以添加新数据
            db.session.begin()
            for index, row in df.iterrows():
                student_id = str(row['学号']).strip()
                name = str(row['姓名']).strip()
                grade = str(row['年级']).strip()
                major = str(row['专业']).strip()
                class_name = str(row['班级']).strip()
                
                # 检查是否为空
                if not student_id or not name or not grade or not major or not class_name:
                    continue
                
                # 添加新记录（因为之前已经清空了表，所以不需要检查是否存在）
                new_student = Student(
                    student_id=student_id,
                    name=name,
                    grade=grade,
                    major=major,
                    class_name=class_name
                )
                db.session.add(new_student)
                imported_count += 1
            
            db.session.commit()
        
        return jsonify({
            'success': True,
            'message': f'学生数据导入成功！已清空原有数据，新增{imported_count}条',
            'imported': imported_count
        })
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'导入失败：{str(e)}'})

# 导入项目数据API
@app.route('/api/import/projects', methods=['POST'])
@admin_required
def import_projects():
    try:
        # 检查是否有文件上传
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '没有文件上传'})
        
        file = request.files['file']
        
        # 检查文件名是否为空
        if file.filename == '':
            return jsonify({'success': False, 'message': '没有选择文件'})
        
        # 检查文件类型是否允许
        if not allowed_file(file.filename):
            return jsonify({'success': False, 'message': '只允许上传Excel文件(xlsx, xls)'})
        
        # 读取Excel文件
        df = pd.read_excel(io.BytesIO(file.read()))
        
        # 检查必要的列是否存在
        required_columns = ['编号', '模块名称', '标准项目类别', '标准项目名称', '认定单位', '学习分']
        for col in required_columns:
            if col not in df.columns:
                return jsonify({'success': False, 'message': f'Excel文件中缺少必要的列：{col}'})
        
        # 导入数据前先清空数据表
        imported_count = 0
        
        with app.app_context():
            # 清空项目表中的所有数据
            db.session.query(Project).delete()
            db.session.commit()
            
            # 重新开始事务以添加新数据
            db.session.begin()
            for index, row in df.iterrows():
                project_id = str(row['编号']).strip()
                module = str(row['模块名称']).strip()
                category = str(row['标准项目类别']).strip()
                name = str(row['标准项目名称']).strip()
                unit = str(row['认定单位']).strip()
                
                # 处理学习分（可能是数字）
                try:
                    score = float(row['学习分'])
                except:
                    continue
                
                # 处理备注（可选）
                remark = str(row['备注']).strip() if '备注' in df.columns and pd.notna(row['备注']) else None
                
                # 检查是否为空
                if not project_id or not module or not category or not name or not unit:
                    continue
                
                # 添加新记录（因为之前已经清空了表，所以不需要检查是否存在）
                new_project = Project(
                    id=project_id,
                    module=module,
                    category=category,
                    name=name,
                    unit=unit,
                    score=score,
                    remark=remark
                )
                db.session.add(new_project)
                imported_count += 1
            
            db.session.commit()
        
        return jsonify({
            'success': True,
            'message': f'项目数据导入成功！已清空原有数据，新增{imported_count}条',
            'imported': imported_count
        })
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'导入失败：{str(e)}'})

# 导入成绩数据API
@app.route('/api/import/scores', methods=['POST'])
@admin_required
def import_scores():
    try:
        # 检查是否有文件上传
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '没有文件上传'})
        
        file = request.files['file']
        
        # 检查文件名是否为空
        if file.filename == '':
            return jsonify({'success': False, 'message': '没有选择文件'})
        
        # 检查文件类型是否允许
        if not allowed_file(file.filename):
            return jsonify({'success': False, 'message': '只允许上传Excel文件(xlsx, xls)'})
        
        # 读取Excel文件
        df = pd.read_excel(io.BytesIO(file.read()))
        
        # 检查必要的列是否存在
        required_columns = ['学号', '编号']
        for col in required_columns:
            if col not in df.columns:
                return jsonify({'success': False, 'message': f'Excel文件中缺少必要的列：{col}'})
        
        # 导入数据前先清空数据表
        imported_count = 0
        skipped_count = 0
        
        with app.app_context():
            # 清空成绩表中的所有数据
            db.session.query(Score).delete()
            db.session.commit()
            
            # 重新开始事务以添加新数据
            db.session.begin()
            for index, row in df.iterrows():
                student_id = str(row['学号']).strip()
                project_id = str(row['编号']).strip()
                
                # 检查是否为空
                if not student_id or not project_id:
                    skipped_count += 1
                    continue
                
                # 检查学生和项目是否存在
                student_exists = Student.query.get(student_id) is not None
                project_exists = Project.query.get(project_id) is not None
                
                if not student_exists or not project_exists:
                    # 记录具体是哪个学生或项目不存在
                    if not student_exists:
                        missing_info = f'学生 {student_id} 不存在'
                    else:
                        missing_info = f'项目 {project_id} 不存在'
                    print(f'跳过记录: {missing_info}')
                    skipped_count += 1
                    continue
                
                # 添加新记录（因为之前已经清空了表，所以不需要检查是否存在）
                new_score = Score(student_id=student_id, project_id=project_id)
                db.session.add(new_score)
                imported_count += 1
            
            db.session.commit()
        
        return jsonify({
            'success': True,
            'message': f'成绩数据导入成功！新增{imported_count}条，跳过{skipped_count}条。\n注意：导入成绩数据前，请确保已先导入学生数据和项目数据！',
            'imported': imported_count,
            'skipped': skipped_count
        })
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'导入失败：{str(e)}'})

# 计算成绩API
@app.route('/api/calculate_scores', methods=['GET'])
@admin_required
def calculate_scores():
    try:
        with app.app_context():
            # 1. 从数据库获取数据并转换成DataFrame
            # 获取学生数据
            students = Student.query.all()
            student_data = []
            for student in students:
                student_data.append({
                    '学号': student.student_id,
                    '姓名': student.name
                })
            df_students = pd.DataFrame(student_data)
            
            # 获取项目数据
            projects = Project.query.all()
            project_data = []
            for project in projects:
                project_data.append({
                    '编号': project.id,
                    '模块名称': project.module,
                    '学习分': project.score
                })
            df_projects = pd.DataFrame(project_data)
            
            # 获取成绩数据
            scores = Score.query.all()
            score_data = []
            for score in scores:
                score_data.append({
                    '学号': score.student_id,
                    '编号': score.project_id
                })
            df_scores = pd.DataFrame(score_data)
            
            # 合并学生、成绩和项目数据
            merged_scores_students = pd.merge(df_scores, df_students, on='学号', how='left')
            merged_df = pd.merge(merged_scores_students, df_projects, on='编号', how='left')
            
            # 2. 应用计算规则 - 从JSON配置文件读取
            try:
                # 获取当前文件所在目录
                current_dir = os.path.dirname(os.path.abspath(__file__))
                # 构建JSON文件的绝对路径
                rules_file_path = os.path.join(current_dir, 'calculation_rules.json')
                
                # 读取JSON配置文件
                with open(rules_file_path, 'r', encoding='utf-8') as f:
                    config = json.load(f)
                    rules = config.get('rules', {})
                
                # 如果配置文件中没有规则，使用默认规则
                if not rules:
                    rules = {
                        '仅计其中最高分': [['J1101', 'J1102'], ['J1201', 'J1202', 'J1203', 'J1204', 'J1205'],
                        ['J1301', 'J1302'], ['J1401', 'J1402'], ['J1501', 'J1502'],
                        ['J1601', 'J1602', 'J1603', 'J1604'], ['J2503', 'J2504'],
                        ['J2201', 'J2202', 'J2203', 'J2204', 'J2205', 'J2206']],
                        '最高计1分': ['J1801', 'J1802'],
                        '最多计2分': ['J1901', 'J1902', 'J1903', 'J1904'],
                        '仅计1项': ['J4101', 'J4102']
                    }
                    app.logger.warning('未找到有效的计算规则配置，使用默认规则')
                
            except Exception as e:
                # 如果读取文件失败，使用默认规则
                app.logger.error(f'读取计算规则配置文件失败: {str(e)}')
                rules = {
                    '仅计其中最高分': [['J1101', 'J1102'], ['J1201', 'J1202', 'J1203', 'J1204', 'J1205'],
                    ['J1301', 'J1302'], ['J1401', 'J1402'], ['J1501', 'J1502'],
                    ['J1601', 'J1602', 'J1603', 'J1604'], ['J2503', 'J2504'],
                    ['J2201', 'J2202', 'J2203', 'J2204', 'J2205', 'J2206']],
                    '最高计1分': ['J1801', 'J1802'],
                    '最多计2分': ['J1901', 'J1902', 'J1903', 'J1904'],
                    '仅计1项': ['J4101', 'J4102']
                }
            
            highest_score_dfs = []
            for code_list in rules['仅计其中最高分']:
                sub_data = merged_df[merged_df['编号'].isin(code_list)]
                if not sub_data.empty:
                    highest_score = sub_data.groupby(['学号', '姓名']).apply(lambda x: x.nlargest(1, '学习分')).reset_index(drop=True)
                    highest_score_dfs.append(highest_score)
            highest_score_data = pd.concat(highest_score_dfs) if highest_score_dfs else pd.DataFrame()
            
            # 处理最高计1分规则 - 支持多个规则组
            max_1_point_dfs = []
            # 确保规则是数组形式，兼容单数组和多维数组格式
            max_1_point_rules = rules['最高计1分'] if isinstance(rules['最高计1分'][0], list) else [rules['最高计1分']]
            for code_list in max_1_point_rules:
                sub_data = merged_df[merged_df['编号'].isin(code_list)].copy()
                if not sub_data.empty:
                    sub_data.loc[:, '学习分'] = sub_data['学习分'].apply(lambda x: min(x, 1))
                    max_1_point_dfs.append(sub_data)
            max_1_point_data = pd.concat(max_1_point_dfs) if max_1_point_dfs else pd.DataFrame()
            
            # 处理最多计2分规则 - 支持多个规则组
            max_2_points_dfs = []
            # 确保规则是数组形式，兼容单数组和多维数组格式
            max_2_points_rules = rules['最多计2分'] if isinstance(rules['最多计2分'][0], list) else [rules['最多计2分']]
            for code_list in max_2_points_rules:
                sub_data = merged_df[merged_df['编号'].isin(code_list)]
                if not sub_data.empty:
                    max_2_points_sum = sub_data.groupby(['学号', '姓名'])['学习分'].sum().reset_index()
                    max_2_points_sum['学习分'] = max_2_points_sum['学习分'].apply(lambda x: min(x, 2))
                    # 为了后续合并，需要添加模块名称列
                    max_2_points_sum['模块名称'] = max_2_points_sum.apply(lambda row: 
                        df_projects[df_projects['编号'].isin(code_list)]['模块名称'].iloc[0] 
                        if not df_projects[df_projects['编号'].isin(code_list)].empty else '', axis=1)
                    max_2_points_dfs.append(max_2_points_sum)
            max_2_points_sum = pd.concat(max_2_points_dfs) if max_2_points_dfs else pd.DataFrame()
            
            # 处理仅计1项规则 - 支持多个规则组
            only_one_item_dfs = []
            # 确保规则是数组形式，兼容单数组和多维数组格式
            only_one_item_rules = rules['仅计1项'] if isinstance(rules['仅计1项'][0], list) else [rules['仅计1项']]
            for code_list in only_one_item_rules:
                sub_data = merged_df[merged_df['编号'].isin(code_list)]
                if not sub_data.empty:
                    only_one_item_max = sub_data.groupby(['学号', '姓名']).apply(lambda x: x.nlargest(1, '学习分')).reset_index(drop=True)
                    only_one_item_dfs.append(only_one_item_max)
            only_one_item_max = pd.concat(only_one_item_dfs) if only_one_item_dfs else pd.DataFrame()
            
            # 收集所有特殊处理过的编号
            all_special_codes = [code for sub_list in rules['仅计其中最高分'] for code in sub_list]
            # 处理其他规则的编号收集，兼容单数组和多维数组格式
            for rule_name in ['最高计1分', '最多计2分', '仅计1项']:
                rule_data = rules[rule_name]
                if isinstance(rule_data[0], list):
                    all_special_codes.extend([code for sub_list in rule_data for code in sub_list])
                else:
                    all_special_codes.extend(rule_data)
            
            # 处理常规数据
            normal_data = merged_df[~merged_df['编号'].isin(all_special_codes)]
            
            # 3. 合并所有数据
            final_data = pd.DataFrame()
            data_frames_to_concat = []
            
            if not highest_score_data.empty:
                data_frames_to_concat.append(highest_score_data)
            if not max_1_point_data.empty:
                data_frames_to_concat.append(max_1_point_data)
            if not max_2_points_sum.empty:
                data_frames_to_concat.append(max_2_points_sum)
            if not only_one_item_max.empty:
                data_frames_to_concat.append(only_one_item_max)
            if not normal_data.empty:
                data_frames_to_concat.append(normal_data)
            
            if data_frames_to_concat:
                final_data = pd.concat(data_frames_to_concat)
            
            # 4. 按模块汇总分数
            if not final_data.empty:
                grouped_df = final_data.groupby(['学号', '姓名', '模块名称'])['学习分'].sum().reset_index()
                
                # 5. 清空现有计算结果并保存新结果
                db.session.query(CalculationResult).delete()
                db.session.commit()
                
                saved_count = 0
                for _, row in grouped_df.iterrows():
                    student_id = row['学号']
                    name = row['姓名']
                    module_name = row['模块名称']
                    score = row['学习分']
                    
                    # 检查必要字段是否为空
                    if student_id and name and module_name and pd.notna(score):
                        result = CalculationResult(
                            student_id=student_id,
                            name=name,
                            module_name=module_name,
                            score=float(score)
                        )
                        db.session.add(result)
                        saved_count += 1
                
                db.session.commit()
                
                return jsonify({
                    'success': True,
                    'message': f'成绩计算成功！已保存{saved_count}条计算结果。',
                    'saved': saved_count
                })
            else:
                return jsonify({
                    'success': False,
                    'message': '没有数据可供计算。'
                })
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'计算失败：{str(e)}'})

# 获取计算结果API
@app.route('/api/calculation_results', methods=['GET'])
@login_required
def get_calculation_results():
    try:
        with app.app_context():
            results = CalculationResult.query.all()
            
            # 格式化结果
            formatted_results = []
            for result in results:
                formatted_results.append({
                    'id': result.id,
                    'student_id': result.student_id,
                    'name': result.name,
                    'module_name': result.module_name,
                    'score': result.score,
                    'created_at': result.created_at.strftime('%Y-%m-%d %H:%M:%S') if result.created_at else None
                })
            
            return jsonify({
                'success': True,
                'results': formatted_results,
                'count': len(formatted_results)
            })
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取计算结果失败：{str(e)}'})

# 获取学生详细得分来源API
@app.route('/api/student_detail', methods=['GET'])
@login_required
def get_student_detail():
    """获取学生详细得分来源API"""
    student_id = request.args.get('student_id')
    
    if not student_id:
        return jsonify({'success': False, 'message': '缺少学号参数'})
    
    try:
        with app.app_context():
            student = Student.query.get(student_id)
            
            if not student:
                return jsonify({'success': False, 'message': '找不到该学生'})
            
            # 查询该学生的所有得分记录
            scores = Score.query.filter_by(student_id=student_id).all()
            
            # 准备返回数据
            result_scores = []
            for score in scores:
                # 获取项目信息
                project = Project.query.get(score.project_id)
                if project:
                    result_scores.append({
                        'project_id': project.id,
                        'project_name': project.name,
                        'module': project.module,
                        'category': project.category,
                        'score': project.score,
                        'remark': project.remark
                    })
            
            return jsonify({
                'success': True,
                'student': {
                    'student_id': student.student_id,
                    'name': student.name,
                    'grade': student.grade,
                    'major': student.major,
                    'class_name': student.class_name
                },
                'scores': result_scores
            })
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取学生详情失败：{str(e)}'})

# 获取计算规则API
@app.route('/api/get_rules', methods=['GET'])
@login_required
def get_rules():
    try:
        # 获取当前文件所在目录
        current_dir = os.path.dirname(os.path.abspath(__file__))
        # 构建JSON文件的绝对路径
        rules_file_path = os.path.join(current_dir, 'calculation_rules.json')
        
        # 读取JSON配置文件
        with open(rules_file_path, 'r', encoding='utf-8') as f:
            config = json.load(f)
            rules = config.get('rules', {})
        
        return jsonify({
            'success': True,
            'rules': rules
        })
    except Exception as e:
        app.logger.error(f'读取计算规则配置文件失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': f'读取计算规则失败: {str(e)}',
            'rules': {}
        })

# 更新计算规则API
@app.route('/api/update_rules', methods=['POST'])
@admin_required
def update_rules():
    try:
        # 获取请求中的规则数据
        rules_data = request.json.get('rules', {})
        
        if not rules_data:
            return jsonify({
                'success': False,
                'message': '没有提供规则数据'
            })
        
        # 获取当前文件所在目录
        current_dir = os.path.dirname(os.path.abspath(__file__))
        # 构建JSON文件的绝对路径
        rules_file_path = os.path.join(current_dir, 'calculation_rules.json')
        
        # 写入更新后的规则到JSON文件
        with open(rules_file_path, 'w', encoding='utf-8') as f:
            json.dump({'rules': rules_data}, f, ensure_ascii=False, indent=2)
        
        return jsonify({
            'success': True,
            'message': '计算规则更新成功'
        })
    except Exception as e:
        app.logger.error(f'更新计算规则配置文件失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': f'更新计算规则失败: {str(e)}'
        })

# 导出Excel API
@app.route('/api/export_excel', methods=['GET'])
@login_required
def export_excel():
    try:
        with app.app_context():
            results = CalculationResult.query.all()
            
            # 将数据转换为DataFrame
            data = []
            for result in results:
                data.append({
                    '学号': result.student_id,
                    '姓名': result.name,
                    '模块名称': result.module_name,
                    '学习分': result.score
                })
            
            if not data:
                return jsonify({'success': False, 'message': '没有数据可供导出'})
            
            df = pd.DataFrame(data)
            
            # 创建透视表，按学号和姓名分组，模块名称为列
            pivot_df = df.pivot_table(
                index=['学号', '姓名'],
                columns='模块名称',
                values='学习分',
                aggfunc='sum',
                fill_value=0
            ).reset_index()
            
            # 计算总分列
            pivot_df['总分'] = pivot_df.iloc[:, 2:].sum(axis=1)
            
            # 创建Excel文件
            output = io.BytesIO()
            wb = Workbook()
            ws = wb.active
            ws.title = "成绩计算结果"
            
            # 添加表头
            headers = list(pivot_df.columns)
            ws.append(headers)
            
            # 添加数据行
            for row in dataframe_to_rows(pivot_df, index=False, header=False):
                ws.append(row)
            
            # 设置列宽
            for column_cells in ws.columns:
                length = max(len(str(cell.value)) for cell in column_cells) + 2
                ws.column_dimensions[column_cells[0].column_letter].width = length
            
            # 保存到内存
            wb.save(output)
            output.seek(0)
            
            # 发送文件
            current_time = datetime.now().strftime('%Y%m%d%H%M%S')
            filename = f'成绩计算结果_{current_time}.xlsx'
            
            return send_file(
                output,
                as_attachment=True,
                download_name=filename,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            
    except Exception as e:
        return jsonify({'success': False, 'message': f'导出失败：{str(e)}'})

# 在文件末尾修改
# 修改密码路由（所有登录用户可访问）
@app.route('/change_password', methods=['GET', 'POST'])
@login_required
def change_password():
    if request.method == 'POST':
        user_id = session['user_id']
        user = User.query.get(user_id)
        
        # 获取表单数据
        current_password = request.form['current_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']
        
        # 验证当前密码是否正确
        if not user.check_password(current_password):
            return render_template('change_password.html', error='当前密码不正确')
        
        # 验证新密码和确认密码是否一致
        if new_password != confirm_password:
            return render_template('change_password.html', error='两次输入的新密码不一致')
        
        # 验证新密码长度
        if len(new_password) < 6:
            return render_template('change_password.html', error='新密码长度至少为6位')
        
        # 更新密码
        try:
            user.set_password(new_password)
            db.session.commit()
            return render_template('change_password.html', success='密码修改成功')
        except:
            db.session.rollback()
            return render_template('change_password.html', error='密码修改失败，请重试')
    
    # GET请求，显示修改密码表单
    return render_template('change_password.html')

# 完全移除了直接的开发服务器启动代码
# 生产环境部署说明：
# 1. 安装依赖：pip install -r requirements.txt
# 2. 使用Gunicorn启动：gunicorn --bind 0.0.0.0:5000 wsgi:app
# 3. 或使用Waitress（Windows友好）：pip install waitress && waitress-serve --listen=0.0.0.0:5000 wsgi:app

# 如果需要开发模式，取消下面的注释
# if __name__ == '__main__':
#     print("警告：这是开发服务器，仅用于开发环境！")
#     print("生产环境请使用WSGI服务器如Gunicorn或Waitress")
#     app.run(host='0.0.0.0', port=5000, debug=True)